﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Koonsoft.HYCRM.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Koonsoft.HYCRM.DAL
{
    /// <summary>
    /// 订单管理
    /// </summary>
    public class OrderDAL
    {
        private static readonly Database Database = DatabaseFactory.CreateDatabase("KOONCRMDB");

        /// <summary>
        /// 生成订单
        /// </summary>
        /// <param name="order"></param>
        public void CreateOrder(OrderInfo order)
        {
            const string sql = @"Order_CreateOrder";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@orderid", DbType.String, order.OrderID);
                Database.AddInParameter(cmd, "@companyid", DbType.String, order.CompanyID);
                Database.AddInParameter(cmd, "@englishname", DbType.String, order.EnglishCompanyName);
                Database.AddInParameter(cmd, "@chinesename", DbType.String, order.ChineseCompanyName);
                Database.AddInParameter(cmd, "@regionid", DbType.Int32, order.RegisterRegionID);
                Database.AddInParameter(cmd, "@stock", DbType.Int32, order.RegisterStock);
                Database.AddInParameter(cmd, "@registerdate", DbType.DateTime, order.RegisiterDate);
                Database.AddInParameter(cmd, "@clientname", DbType.String, order.ClientName);
                Database.AddInParameter(cmd, "@telephone", DbType.String, order.Telephone);
                Database.AddInParameter(cmd, "@mobile", DbType.String, order.Mobile);
                Database.AddInParameter(cmd, "@address", DbType.String, order.Address);
                Database.AddInParameter(cmd, "@email", DbType.String, order.Email);
                Database.AddInParameter(cmd, "@im", DbType.String, order.IM);
                Database.AddInParameter(cmd, "@fax", DbType.String, order.Fax);
                Database.AddInParameter(cmd, "@clienttype", DbType.Int32, order.ClientType);
                Database.AddInParameter(cmd, "@ordertime", DbType.DateTime, order.OrderTime);
                Database.AddInParameter(cmd, "@currencytype", DbType.Int32, order.CurrencyType);
                Database.AddInParameter(cmd, "@amount", DbType.Int32, order.Amount);
                Database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, order.EmpolyeeID);
                Database.AddInParameter(cmd, "@delegatetype", DbType.Int32, order.DelegateTypeID);
                Database.AddInParameter(cmd, "@remark", DbType.String, order.Remark);
                Database.AddInParameter(cmd, "@branchid", DbType.Int32, order.BranchID);
                Database.AddInParameter(cmd, "@issubsidiary", DbType.Int32, order.IsSubsidiary);
                Database.AddInParameter(cmd, "@parentcompany", DbType.String, order.ParentCompanyID);
                Database.AddInParameter(cmd, "@agentcompany", DbType.String, order.AgentCompanyName);
                Database.AddInParameter(cmd, "@audityear", DbType.Int32, order.AuditYear);

                if (order.DelegateTypeID == 5)
                {
                    Database.AddInParameter(cmd, "@BR", DbType.String, order.BR);
                    Database.AddInParameter(cmd, "@StartTime", DbType.String, order.StartTime);
                    Database.AddInParameter(cmd, "@EndTime", DbType.String, order.EndTime);
                    Database.AddInParameter(cmd, "@BusinessScope", DbType.String, order.BusinessScope);
                    Database.AddInParameter(cmd, "@BusinessAddress", DbType.String, order.BusinessAddress);
                    Database.AddInParameter(cmd, "@TotalTurnover", DbType.String, order.TotalTurnover);
                    Database.AddInParameter(cmd, "@ProfitTax", DbType.String, order.ProfitTax);
                }


                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取日报表
        /// </summary>
        /// <param name="branchName"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="delegateName"></param>
        /// <param name="empolyeeID"></param>
        /// <param name="regionName"></param>
        /// <returns></returns>
        public DataTable GetDailySheet(string branchName, string startTime, string endTime, string delegateName, string regionName, int empolyeeID)
        {
            const string procedure = "Sheet_GetDailySheet";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@branchname", DbType.String, branchName);
                if (!string.IsNullOrEmpty(startTime))
                {
                    Database.AddInParameter(cmd, "@starttime", DbType.DateTime, startTime);
                }
                if (!string.IsNullOrEmpty(endTime))
                {
                    Database.AddInParameter(cmd, "@endtime", DbType.DateTime, endTime);
                }
                Database.AddInParameter(cmd, "@delegateName", DbType.String, delegateName);
                Database.AddInParameter(cmd, "@regionName", DbType.String, regionName);
                Database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);

                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 获取订单详情
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public OrderInfo GetOrderInfoByOrderID(string orderID)
        {
            const string procedure = "Order_GetOrderInfoByOrderID";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return new OrderInfo()
                               {
                                   OrderTime = Convert.ToDateTime(sqlrd["OrderTime"]),
                                   DelegateTypeID = Convert.ToInt32(sqlrd["DelegateType"]),
                                   Amount = Convert.ToInt32(sqlrd["Amount"]),
                                   CurrencyType = (CurrencyType)Convert.ToInt32(sqlrd["CurrencyType"]),
                                   BranchID = Convert.ToInt32(sqlrd["BranchID"]),
                                   EmpolyeeID = Convert.ToInt32(sqlrd["EmpolyeeID"]),
                                   Remark = sqlrd["Remark"].ToString(),
                                   ChineseCompanyName = sqlrd["ChineseName"].ToString(),
                                   EnglishCompanyName = sqlrd["EnglishName"].ToString(),
                                   Address = sqlrd["Address"].ToString(),
                                   ClientName = sqlrd["ClientName"].ToString(),
                                   Telephone = sqlrd["Telephone"].ToString(),
                                   Mobile = sqlrd["Mobile"].ToString(),
                                   Email = sqlrd["Email"].ToString(),
                                   IM = sqlrd["IM"].ToString(),
                                   Fax = sqlrd["Fax"].ToString(),
                                   ClientType = (ClientType)Convert.ToInt32(sqlrd["Type"])
                               };
                }
                return null;
            }
        }

        /// <summary>
        /// 根据订单号获取公司编号
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public string GetCompanySerialNumberByOrderID(string orderID)
        {
            const string sql = "select companyserialnumber from [order] where orderid=@orderid";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@orderid", DbType.String, orderID);

                return Database.ExecuteScalar(cmd).ToString();
            }
        }


        /// <summary>
        /// 根据公司编号获取最近的交易记录
        /// </summary>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public static LastDealInfo GetLastDealInfoByCompanySerialNumber(string serialNumber)
        {
            const string sql = @"Order_GetLastDealInfo";

            SqlDataReader sqlrd;

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };

            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.StoredProcedure,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@companyserialnumber", SqlDbType.NVarChar, 36);
            sqlcmd.Parameters["@companyserialnumber"].Value = serialNumber;

            sqlcmd.Parameters.Add("@delegatetype", SqlDbType.SmallInt);

            #region 先查年检是否有记录

            sqlcmd.Parameters["@delegatetype"].Value = 4;

            try
            {
                sqlcmd.Connection.Open();
                sqlrd = sqlcmd.ExecuteReader();

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    return new LastDealInfo
                    {
                        Amount = Convert.ToInt32(sqlrd["amount"]),
                        BranchName = sqlrd["BranchName"].ToString(),
                        EmployeeName = sqlrd["Name"].ToString(),
                        ClientName = sqlrd["ClientName"].ToString(),
                        ClientTelephone = sqlrd["Telephone"].ToString(),
                        ClientType = sqlrd["clienttype"].ToString(),
                        ClientMobile = sqlrd["Mobile"].ToString()

                    };
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                sqlcmd.Connection.Close();
            }

            #endregion

            #region 再查注册是否有记录

            sqlcmd.Parameters["@delegatetype"].Value = 3;

            try
            {
                sqlcmd.Connection.Open();
                sqlrd = sqlcmd.ExecuteReader();

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    return new LastDealInfo
                    {
                        Amount = Convert.ToInt32(sqlrd["amount"]),
                        BranchName = sqlrd["BranchName"].ToString(),
                        EmployeeName = sqlrd["Name"].ToString(),
                        ClientName = sqlrd["ClientName"].ToString(),
                        ClientTelephone = sqlrd["Telephone"].ToString(),
                        ClientType = sqlrd["clienttype"].ToString(),
                        ClientMobile = sqlrd["Mobile"].ToString()
                    };
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                sqlcmd.Connection.Close();
            }

            #endregion

            #region 最后查找单的记录

            sqlcmd.Parameters["@delegatetype"].Value = 31;

            try
            {
                sqlcmd.Connection.Open();
                sqlrd = sqlcmd.ExecuteReader();

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    return new LastDealInfo
                    {
                        Amount = Convert.ToInt32(sqlrd["amount"]),
                        BranchName = sqlrd["BranchName"].ToString(),
                        EmployeeName = sqlrd["Name"].ToString(),
                        ClientName = sqlrd["ClientName"].ToString(),
                        ClientTelephone = sqlrd["Telephone"].ToString(),
                        ClientType = sqlrd["clienttype"].ToString(),
                        ClientMobile = sqlrd["Mobile"].ToString()
                    };
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                sqlcmd.Connection.Close();
            }

            #endregion

            return null;
        }

        /// <summary>
        /// 检查是否有同英文名同注册地的公司在同一天已办理了同类型的业务
        /// </summary>
        /// <returns></returns>
        public static bool CheckOrderExists(string englishName, int regionID, DateTime ordertime, int delegateType)
        {
            const string procedure = @"Order_CheckOrderExsists";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@englishname", DbType.String, englishName);
                Database.AddInParameter(cmd, "@regionid", DbType.Int32, regionID);
                Database.AddInParameter(cmd, "@delegatetype", DbType.Int32, delegateType);
                Database.AddInParameter(cmd, "@ordertime", DbType.DateTime, ordertime);

                return (int)Database.ExecuteScalar(cmd) > 0;
            }
        }

        /// <summary>
        /// 按员工统计订单
        /// </summary>
        /// <returns></returns>
        public DataTable StatisticOrdersGroupByEmpolyee(string startTime, string endTime)
        {
            const string sql = "Statistic_Empolyee";

            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                if (!string.IsNullOrEmpty(startTime))
                {
                    Database.AddInParameter(cmd, "@starttime", DbType.DateTime, Convert.ToDateTime(startTime));
                }

                if (!string.IsNullOrEmpty(endTime))
                {
                    Database.AddInParameter(cmd, "@endtime", DbType.DateTime, Convert.ToDateTime(endTime));
                }

                return Database.ExecuteDataSet(cmd).Tables[0];
            }

        }

        /// <summary>
        /// 按分行统计订单
        /// </summary>
        /// <returns></returns>
        public DataTable StatisticOrdersGroupByBranch(string startTime, string endTime)
        {
            const string procedure = "Statistic_Branch";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                if (!string.IsNullOrEmpty(startTime))
                {
                    Database.AddInParameter(cmd, "@starttime", DbType.DateTime, Convert.ToDateTime(startTime));
                }

                if (!string.IsNullOrEmpty(endTime))
                {
                    Database.AddInParameter(cmd, "@endtime", DbType.DateTime, Convert.ToDateTime(endTime));
                }

                return Database.ExecuteDataSet(cmd).Tables[0];
            }

        }

        /// <summary>
        /// 供总编处理的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetHongKongFileEditorOrders()
        {
            const string procedure = "Order_HongKongFileEditor";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 供银行助理处理的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetHongKongFileHelperOrders()
        {
            const string procedure = "Order_HongKongFileHelper";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 供银行助理处理的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetHongKongFileHelperOrders(int delegateID,int regionID,int branchID,string empolyeeName)
        {
            const string procedure = "Order_HongKongFileHelper";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@delegateID", DbType.Int32, delegateID);
                Database.AddInParameter(cmd, "@regionID", DbType.Int32, regionID);
                Database.AddInParameter(cmd, "@branchID", DbType.Int32, branchID);
                Database.AddInParameter(cmd, "@empolyeeName", DbType.String, empolyeeName);

                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        } 


        public DataTable GetAuditorsForUser(string loginName)
        {
            const string procedure = "Order_GetAuditorsForUser";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@loginName", DbType.String, loginName);
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 供其它（非香港）文件处理的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetOtherRegionFileHandlerOrders()
        {
            const string procedure = "Order_OtherRegionFileHandler";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 获取等待总行快递的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetDeliverOrders(int empolyeeID,int ordermonth,int regionID,int branchID,int delegateID)
        {
            const string procedure = "Order_WaitForDeliver";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);
                Database.AddInParameter(cmd, "@orderMonth", DbType.Int32, ordermonth);
                Database.AddInParameter(cmd, "@regionID", DbType.Int32, regionID);
                Database.AddInParameter(cmd, "@branchID", DbType.Int32, branchID);
                Database.AddInParameter(cmd, "@delegateID", DbType.Int32, delegateID);
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 获取等待分行快递核对全款的订单
        /// </summary>
        /// <returns></returns>
        public DataTable GetAuditOrders(int empolyeeID)
        {
            const string procedure = "Order_FinanceAudit";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 改变订单状态
        /// </summary>
        /// <param name="orderID"></param>
        /// <param name="orderStatus"></param>
        public void ChangeOrderStatusByOrderID(string orderID, OrderStatus orderStatus)
        {
            const string procedure = "Order_ChangeStatusByOrderID";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@Status", DbType.Int32, (int)orderStatus);
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderID);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 根据订单号获取订单总入账金额
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public int GetIncomeByOrderID(string orderID)
        {
            const string sql = "select SUM(amount) from payment where OrderID=@orderid";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@orderID", DbType.String, orderID);

                return Database.ExecuteScalar(cmd) == DBNull.Value ? 0 : Convert.ToInt32(Database.ExecuteScalar(cmd));
            }
        }

        /// <summary>
        /// 获取最近一次报单的业务类型是否为找单
        /// </summary>
        /// <param name="companyID"></param>
        /// <returns></returns>
        public bool GetLastOrderInfoByCompanyID(string companyID)
        {
            const string sql = @"select top 1 * from [Order] where CompanySerialNumber=(
select top 1 CompanySerialNumber from [company] where CompanyID=@companyID)
order by OrderTime desc";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@companyID", DbType.String, companyID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    if (sqlrd["delegatetype"].ToString().Equals("52"))
                    {
                        return true;
                    }
                }

                return false;
            }
        }

        
        /// <summary>
        /// 获取年检参考
        /// </summary>
        /// <returns></returns>
        public DataTable GetYearAuditRefrence()
        {
            const string procedure = "Sheet_YearAuditForEditor";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                return Database.ExecuteDataSet(cmd).Tables[0];
            }
        }

        /// <summary>
        /// 取消订单
        /// </summary>
        /// <param name="orderID"></param>
        /// <param name="operatorID"></param>
        /// <param name="remark"></param>
        public void CancelOrderByOperator(string orderID,int operatorID,string remark)
        {
            const string procedure = "Order_Canceled";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd,"@orderid",DbType.String,orderID);
                Database.AddInParameter(cmd, "@operator", DbType.Int32, operatorID);
                Database.AddInParameter(cmd,"@remark",DbType.String,remark);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 获取订单扩展信息
        /// </summary>
        /// <param name="orderId"></param>
        public OrderExpansion GetOrderExpansion(string orderId)
        {
            const string procedure = "select * From OrderExpansion where orderid = @OrderID";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderId);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return new OrderExpansion()
                    {
                      
                        Remark = sqlrd["Remark"].ToString(),
                        Id = sqlrd["id"].ToString(),
                        OrderID = sqlrd["OrderID"].ToString()
                    };
                }
                return null;
            }
        }

        public void UpdateOrderExpansionRemark(string orderID, string Remark)
        {
            const string procedure = @"
if exists(select * From OrderExpansion where orderid = @orderid)
begin
	update OrderExpansion set remark = @remark where orderid = @orderid
end
else
begin
	insert into OrderExpansion(orderid,remark) values(@orderid,@remark)
end
";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@orderid", DbType.String, orderID);
                Database.AddInParameter(cmd, "@remark", DbType.String, Remark);

                Database.ExecuteNonQuery(cmd);
            }
        }

    }

}
